-用户权限管理，共需要五张表，users表已经有了，此处只有四张表

create table permission 
(
   id                 number(10)           not null,
   permission         varchar2(50),
   url                varchar2(200),
   remark              varchar2(100),
   constraint PK_PERMISSION primary key (id)
);
create sequence permission_id minvalue 0 start with 0 increment by 1;

insert into permission values(permission_id.nextval,'用户信息管理','user.servlet?param=init','用户权限管理模块中的用户信息管理部分');
insert into permission values(permission_id.nextval,'角色信息管理','role.servlet?param=init','用户权限管理模块中的角色信息管理部分');
insert into permission values(permission_id.nextval,'权限信息管理','permission.servlet?param=init','用户权限管理模块中的权限信息管理部分');
insert into permission values(permission_id.nextval,'写文章','article.servlet','用户权限管理模块中的用用户信息管理部分');
insert into permission values(permission_id.nextval,'文章类型管理','articleType.servlet','用户权限管理模块中的用用户信息管理部分');
commit;
select * from permission;

create table permissionRole 
(
   id                 number(10)           not null,
   permissionid       number(10),
   roleid             number(10),
   constraint PK_PERMISSIONROLE primary key (id)
);
create sequence permissionRole_id minvalue 0 start with 0 increment by 1;

--为超级管理员角色添加权限
insert into permissionRole values(Permissionrole_id.nextval,1,1);
insert into permissionRole values(Permissionrole_id.nextval,2,1);
insert into permissionRole values(Permissionrole_id.nextval,3,1);
insert into permissionRole values(Permissionrole_id.nextval,4,1);
insert into permissionRole values(Permissionrole_id.nextval,5,1);
--为一般用户角色添加权限
insert into permissionRole values(Permissionrole_id.nextval,2,2);
commit;

create table role
(
   id                 number(10)    not null,
   rolename           varchar(50),
   remark               varchar(100),
   constraint PK_ROLE primary key (id)
);
create sequence role_id minvalue 0 start with 0 increment by 1;

insert into role values(role_id.nextval,'超级管理员','拥有所有权限角色');
insert into role values(role_id.nextval,'用户','一般用户角色');
commit;
select * from role;

create table userRole 
(
   id                 number(10)           not null,
   userid            number(10)         references users(id),
   roleid            number(10)        references role(id),
   constraint PK_USERROLE primary key (id)
);
create sequence userRole_id minvalue 0 start with 0 increment by 1;

--为admin用户添加超级管理员角色
insert into userRole values(Userrole_Id.nextval,1,1);
--为test用户添加一般用户角色
insert into userRole values(Userrole_Id.nextval,2,2);
commit;

--根据用户id获取该用户权限
select p.permission,p.url from users u left outer join userRole ur on u.id=ur.userId
    left outer join permissionRole pr on ur.roleid=pr.roleId
    left outer join permission p on pr.permissionId=p.id
    where u.name='admin';